import pandas as pd
import pymysql
from sqlalchemy import create_engine

# 读取 Excel 文件
excel_file = 'Ranking.xls'  # Excel 文件路径
df = pd.read_excel(excel_file, header=0)
# 使用 replace 方法将所有空格替换为空
df = df.replace(to_replace=r'^\s*$', value='0', regex=True)

columns_to_drop = ["年级", "学院", "类型", "层次", "在校"]  # 要删除的列名列表
df.drop(columns_to_drop, axis=1, inplace=True)
# 指定要写入的列，确保顺序和数据库表中的列一致
columns = ["姓名", "学号", "性别", "班级", "总学分", "必修", "限选", "任选", "体育", "不及格", "必修课平均绩点",
           "课程平均绩点", "必修课专业排名", "全部课程排名", "专业"]

df = df[columns]
# 使用rename方法更改列名
df = df.rename(columns={
    "姓名": "name",
    "学号": "id",
    "性别": "sex",
    "班级": "class",
    "总学分": "total_credits",
    "必修": "compulsory",
    "限选": "limited_choice",
    "任选": "free_choice",
    "体育": "pe",
    "不及格": "fail",
    "必修课平均绩点": "average_compulsory",
    "课程平均绩点": "average_all_courses",
    "必修课专业排名": "rank_compulsory",
    "全部课程排名": "rank_all_course",
    "专业": "major"
})
print(df)

# 创建一个数据库连接名
engine = create_engine('mysql+pymysql://root:20011231ZENG@localhost:3306/pycharm')

# 使用to_sql方法将DataFrame写入数据库

df.to_sql('rank', con=engine, if_exists='append', index=False)
